import pymysql

# 连接数据库
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    passwd='123456',
    charset='utf8'
)
cursor = conn.cursor()

# 创建数据库
# cursor.execute('create database test01 CHARACTER SET utf8mb4 collate utf8mb4_0900_ai_ci')
# conn.commit()

# 进入数据库 查看数据表
cursor.execute('use xrg')
cursor.execute('show tables')
result = cursor.fetchall()
print(result)

# 进入数据库 创建表
cursor.execute('use test01')
sql = '''
CREATE TABLE `role` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '角色编号',
  `username` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '名字',
  `password` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '密码',
  `nickname` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '昵称/简称',
  `user_face` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '角色头像',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB COMMENT='角色表';
'''
# cursor.execute(sql)
# conn.commit()

# 增加一些数据
insert_sql1 = '''
INSERT INTO `test01`.`role` (`id`, `username`, `password`, `nickname`, `user_face`, `create_time`, `update_time`) VALUES (1, '佰发', '123', 'duiduo', NULL, '2023-01-06 15:33:27', '2023-01-06 15:33:30')
'''
# cursor.execute(insert_sql1)
# conn.commit()

insert_sql2 = '''
INSERT INTO `test01`.`role` (`id`, `username`, `password`, `nickname`, `user_face`, `create_time`, `update_time`) VALUES (2, '晓黑', '123', 'XiaoHei_CCsqr', NULL, '2023-01-06 15:33:33', '2023-01-06 15:33:35')
'''
# cursor.execute(insert_sql2)
# conn.commit()

# 其他省略 都是一样的 cursor.execute(sql)  conn.commit()

# 关闭连接
cursor.close()
conn.close()